
-- Hovercraft.OldDesign contains no duplicated of New Design
SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign EXCEPT SELECT HovercraftModelID, Name FROM Hovercraft.NewDesign ORDER BY Name


-- Union(remove all duplicate records)
-- Union All(faster than union and keep duplicate things)


-- 'with encryption' will not see by other people
CREATE PROCEDURE HumanResources.uspNamePhoneEmail
WITH ENCRYPTION
AS
   SET NOCOUNT ON;
   SELECT FirstName, LastName, EmailAddress, PhoneNumber
   FROM HumanResources.vEmployee;


-- ROW_NUMBER() shows row number for each column
SELECT BusinessEntityID, Rate, ROW_NUMBER() OVER (ORDER BY Rate DESC) as Rank
FROM Adventureworks2012.HumanResources.EmployeePayHistory
WHERE PayFrequency = 2;

-- RANK()
SELECT BusinessEntityID, Rate, RANK() OVER (ORDER BY Rate DESC) as Rank
FROM Adventureworks2012.HumanResources.EmployeePayHistory
WHERE PayFrequency = 2;

-- 1 - 5 for all items
SELECT BusinessEntityID, Rate, NTILE(5) OVER (ORDER BY Rate DESC) as Rank
FROM Adventureworks2012.HumanResources.EmployeePayHistory
WHERE PayFrequency = 2;


SELECT BusinessEntityID, Rate, DENSE_RANK() OVER (ORDER BY Rate DESC) as Rank
FROM Adventureworks2012.HumanResources.EmployeePayHistory
WHERE PayFrequency = 2;

   

-- run it on first windows say unitprice is 20.0
BEGIN TRAN;
  SELECT productid, unitprice
  FROM dbo.Products
  WHERE productid = 2;

-- run it on second window  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT productid, unitprice
FROM dbo.Products
WHERE productid = 2;

-- run 'ROLLBACK TRAN;' in first window
ROLLBACK TRAN;
-- then the unitprice is still 19.0

CREATE TABLE Hovercraft (HovercraftID uniqueidentifier DEFAULT NEWSEQUENTIALID() PRIMARY KEY)

-- Creates a schema in the current database. The CREATE SCHEMA transaction can also create tables and views within the new schema, and set GRANT, DENY, or REVOKE permissions on those objects.

USE AdventureWorks2012;
GO
CREATE SCHEMA Sprockets AUTHORIZATION Annik
    CREATE TABLE NineProngs (source int, cost int, partnumber int)
    GRANT SELECT ON SCHEMA::Sprockets TO Mandar
    DENY SELECT ON SCHEMA::Sprockets TO Prasanna;
GO 

-- replace NULL to 0
SELECT SpecialOfferID, Description, Category, DiscountPct, MinQty, COALESCE(MaxQty, 0) as 'MaxQty' FROM Sales.SpecialOffer;

-- if ISNULL to 0
SELECT SpecialOfferID, Description, Category, DiscountPct, MinQty, ISNULL(MaxQty, 0) as 'MaxQty' FROM Sales.SpecialOffer;


-- The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
-- STUFF ( character_expression , start , length , replaceWith_expression )
SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO


declare @tempDoc xml
set @tempDoc = '<Root>
<ProductDescription ProductID="1" ProductName="Hovercraft">
<Features>
  <Passengers>60 passenger capacity</Passengers>
  <Performance>Top speed: 200 KMPH</Performance>
  <Maintenance>5 year parts and labor extended maintenance </Maintenance>
</Features>
</ProductDescription>
</Root>';
SELECT @tempDoc.query('/Root/ProductDescription/Features/Maintenance');


--Use the IN clause to copy the table into another database:
SELECT *
INTO CustomersBackup2013 IN 'Backup.mdb'
FROM Customers;


-- hash join
SELECT ph.PurchaseOrderID, ph.OrderDate, pd.ProductID, pd.DueDate, ph.VendorID
FROM Purchasing.PurchaseOrderHeader AS ph
INNER HASH JOIN Purchasing.PurchaseOrderDetail AS pd
    ON ph.PurchaseOrderID = pd.PurchaseOrderID;


-- Union and intersect priority test
use Test
go
create table a (col int)
insert into a (col) values (1), (2), (3), (4), (5), (96), (97), (98), (99), (100)

create table b (col int)
insert into b (col) values (1), (98), (2), (99), (3), (100)

create table c (col int)
insert into c (col) values (1), (98), (96), (99), (8), (100)

(select col from a Union select col from b) intersect select col from c    


IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety ON DATABASE;
GO
CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT 'You must disable Trigger "safety" to drop or alter tables!' 
   ROLLBACK;
GO
DISABLE TRIGGER safety ON DATABASE;
GO
ENABLE TRIGGER safety ON DATABASE;
GO


-- TRY_PARSE
DECLARE @StringVal VARCHAR(20)
SET @StringVal='13-OCT-2012 10:00:00'
SELECT TRY_PARSE(@StringVal AS DATETIME)
SET @StringVal='13OCT2012 10:00:00'
SELECT TRY_PARSE(@StringVal AS DATETIME)
SET @StringVal='2012OCT13 10:00:00'
SELECT TRY_PARSE(@StringVal AS DATETIME)


-- SchemaBinding 
CREATE TABLE dbo.SUPPLY1 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),
supplier CHAR(50)
);
INSERT dbo.SUPPLY1 VALUES ('1', 'CaliforniaCorp'), ('5', 'BraziliaLtd')
, ('231', 'FarEast'), ('280', 'NZ')
, ('321', 'EuroGroup'), ('442', 'UKArchip')
, ('475', 'India'), ('521', 'Afrique');
GO
CREATE VIEW dbo.all_supplier_view
WITH SCHEMABINDING
AS
SELECT supplyID, supplier
FROM dbo.SUPPLY1

Drop table dbo.SUPPLY1

alter view dbo.all_supplier_view
WITH SCHEMABINDING
AS
SELECT supplyID
FROM dbo.SUPPLY1
-- Msg 208, Level 16, State 1, Procedure all_supplier_view, Line 4
-- Invalid object name 'dbo.SUPPLY1'.
-- Msg 4413, Level 16, State 1, Line 1
-- Could not use view or function 'dbo.all_supplier_view' because of binding errors.
